﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Shapes;
using System.Data;
using System.Collections.ObjectModel;

namespace Kiosk
{
    /// <summary>
    /// Interaction logic for UserFilters.xaml
    /// </summary>
    public partial class FilterMentors : Window
    {
        private ObservableCollection<Mentor> filterMentors;
        private bool boolStopFilterEvents;
        private List<string> listDegree;
        private List<string> listExperience;
        private List<string> listCourses;
        private List<string> listLocation;
        private List<string> listJobType;
        private List<KeyValuePair<string, string>> listDegreeKVP;
        private List<KeyValuePair<string, string>> listExperienceKVP;
        private List<KeyValuePair<string, string>> listCoursesKVP;
        private List<KeyValuePair<string, string>> listLocationKVP;
        private List<KeyValuePair<string, string>> listJobTypeKVP;
       
        private DataSet dsFilterMentors; //the returned dataset from the SQL query
        private int returnCount = 0; //the amount of rows returned from the query.
        string filterQueryString = "";
        bool pageReady = false;
        private List<string> filterQueryWhereParams;

        public FilterMentors()
        {
            InitializeComponent();
            this.filterMentors = new ObservableCollection<Mentor>();
            this.boolStopFilterEvents = false;
            #region buildSelectionLists

            listDegree = new List<string>();
            listExperience = new List<string>();
            listCourses = new List<string>();
            listLocation = new List<string>();
            listJobType = new List<string>();
            listDegreeKVP = new List<KeyValuePair<string, string>>();
            listExperienceKVP = new List<KeyValuePair<string, string>>();
            listCoursesKVP = new List<KeyValuePair<string, string>>();
            listLocationKVP = new List<KeyValuePair<string, string>>();
            listJobTypeKVP = new List<KeyValuePair<string, string>>();
            dsFilterMentors = new DataSet();

            listDegree.Add(" ");
            listDegreeKVP.Add(new KeyValuePair<string, string>(" ", " "));
            listExperience.Add(" ");
            listExperienceKVP.Add(new KeyValuePair<string, string>(" ", " "));
            listCourses.Add(" ");
            listCoursesKVP.Add(new KeyValuePair<string, string>(" ", " "));
            listLocation.Add(" ");
            listLocationKVP.Add(new KeyValuePair<string, string>(" ", " "));
            listJobType.Add(" ");
            listJobTypeKVP.Add(new KeyValuePair<string, string>(" ", " "));

            string sql = "SELECT degree_name, degree_id FROM lkup_degree";
            dsFilterMentors = sGlobals.dbWrapper.ExecuteSQLStatement(sql, ref returnCount);
            if (returnCount > 0)
            {
                for (int i = 0; i < dsFilterMentors.Tables[0].Rows.Count; i++)
                {
                    listDegree.Add(dsFilterMentors.Tables[0].Rows[i]["degree_name"].ToString());
                    listDegreeKVP.Add(new KeyValuePair<string, string>(dsFilterMentors.Tables[0].Rows[i]["degree_name"].ToString(), dsFilterMentors.Tables[0].Rows[i]["degree_id"].ToString()));
                }
                Filter_Education.ItemsSource = listDegree;
            }

            dsFilterMentors = new DataSet();
            sql = "SELECT job_type_text, job_type_id FROM lkup_job_type";
            dsFilterMentors = sGlobals.dbWrapper.ExecuteSQLStatement(sql, ref returnCount);
            if (returnCount > 0)
            {
                for (int i = 0; i < dsFilterMentors.Tables[0].Rows.Count; i++)
                {
                    listJobType.Add(dsFilterMentors.Tables[0].Rows[i]["job_type_text"].ToString());
                    listJobTypeKVP.Add(new KeyValuePair<string, string>(dsFilterMentors.Tables[0].Rows[i]["job_type_text"].ToString(), dsFilterMentors.Tables[0].Rows[i]["job_type_id"].ToString()));
                }
                Filter_Job_Type.ItemsSource = listJobType;
            }

            dsFilterMentors = new DataSet();
            sql = "SELECT location_text, location_id FROM lkup_location";
            dsFilterMentors = sGlobals.dbWrapper.ExecuteSQLStatement(sql, ref returnCount);
            if (returnCount > 0)
            {
                for (int i = 0; i < dsFilterMentors.Tables[0].Rows.Count; i++)
                {
                    listLocation.Add(dsFilterMentors.Tables[0].Rows[i]["location_text"].ToString());
                    listLocationKVP.Add(new KeyValuePair<string, string>(dsFilterMentors.Tables[0].Rows[i]["location_text"].ToString(), dsFilterMentors.Tables[0].Rows[i]["location_id"].ToString()));
                }
                Filter_Location.ItemsSource = listLocation;
            }

            dsFilterMentors = new DataSet();
            sql = "SELECT range_text, range_id FROM lkup_year_range";
            dsFilterMentors = sGlobals.dbWrapper.ExecuteSQLStatement(sql, ref returnCount);
            if (returnCount > 0)
            {
                for (int i = 0; i < dsFilterMentors.Tables[0].Rows.Count; i++)
                {
                    listExperience.Add(dsFilterMentors.Tables[0].Rows[i]["range_text"].ToString());
                    listExperienceKVP.Add(new KeyValuePair<string, string>(dsFilterMentors.Tables[0].Rows[i]["range_text"].ToString(), dsFilterMentors.Tables[0].Rows[i]["range_id"].ToString()));
                }
                Filter_Experience.ItemsSource = listExperience;
            }

            dsFilterMentors = new DataSet();
            sql = "SELECT course_name, course_id FROM lkup_courses";
            dsFilterMentors = sGlobals.dbWrapper.ExecuteSQLStatement(sql, ref returnCount);
            if (returnCount > 0)
            {
                for (int i = 0; i < dsFilterMentors.Tables[0].Rows.Count; i++)
                {
                    listCourses.Add(dsFilterMentors.Tables[0].Rows[i]["course_name"].ToString());
                    listCoursesKVP.Add(new KeyValuePair<string, string>(dsFilterMentors.Tables[0].Rows[i]["course_name"].ToString(), dsFilterMentors.Tables[0].Rows[i]["course_id"].ToString()));
                }
                Filter_Courses_1.ItemsSource = listCourses;
            }

            #endregion

            buildMentorQuery(null);
        }

        /// <summary>
        /// This method logs the user out of the kiosk. It is called when the user presses the logout button.
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnLogout_Click(object sender, RoutedEventArgs e)
        {
            sGlobals.logout();
        }

        private void buildMentorQuery(ComboBox tempCB)
        {
            dsFilterMentors = new DataSet();
            this.filterMentors.Clear();
            filterQueryString = "";
            filterQueryWhereParams = new List<string>();

            if (Filter_Gender.SelectedItem != null)
            {
                switch (((ComboBoxItem)Filter_Gender.SelectedItem).Content.ToString())
                {
                    case "Male":
                        filterQueryWhereParams.Add("gender = 'M'");
                        break;
                    case "Female":
                        filterQueryWhereParams.Add("gender = 'F'");
                        break;
                    default:
                        break;
                }
            }
            if (Filter_Education.SelectedItem != null) 
            {
                foreach (KeyValuePair<string, string> currKVP in listDegreeKVP)
                {
                    if (Filter_Education.SelectedItem.ToString() == " ")
                    {
                        break;
                    }
                    if (Filter_Education.SelectedItem.ToString() == currKVP.Key)
                    {
                        filterQueryWhereParams.Add("degree = '" + currKVP.Value + "'");
                    }
                }
            }
            if (Filter_Courses_1.SelectedItem != null)
            {
                foreach (KeyValuePair<string, string> currKVP in listCoursesKVP)
                {
                    if (Filter_Courses_1.SelectedItem.ToString() == " ")
                    {
                        break;
                    }
                    if (Filter_Courses_1.SelectedItem.ToString() == currKVP.Key)
                    {
                        filterQueryWhereParams.Add("(benneficial_course1 = '" + currKVP.Value + "' OR benneficial_course2 = '" + currKVP.Value + "' OR benneficial_course3 = '" + currKVP.Value + "')");
                    }
                }
            }
            if (Filter_Experience.SelectedItem != null)
            {
                foreach (KeyValuePair<string, string> currKVP in listExperienceKVP)
                {
                    if (Filter_Experience.SelectedItem.ToString() == " ")
                    {
                        break;
                    }
                    if (Filter_Experience.SelectedItem.ToString() == currKVP.Key)
                    {
                        filterQueryWhereParams.Add("years_experience_group = '" + currKVP.Value + "'");
                    }
                }
            }
            if (Filter_Job_Type.SelectedItem != null)
            {
                foreach (KeyValuePair<string, string> currKVP in listJobTypeKVP)
                {
                    if (Filter_Job_Type.SelectedItem.ToString() == " ")
                    {
                        break;
                    }
                    if (Filter_Job_Type.SelectedItem.ToString() == currKVP.Key.ToString())
                    {
                        filterQueryWhereParams.Add("job_type = '" + currKVP.Value + "'");
                    }
                }
            }
            if (Filter_Location.SelectedItem != null)
            {
                foreach (KeyValuePair<string, string> currKVP in listLocationKVP)
                {
                    if (Filter_Location.SelectedItem.ToString() == " ")
                    {
                        break;
                    }
                    if (Filter_Location.SelectedItem.ToString() == currKVP.Key)
                    {
                        filterQueryWhereParams.Add("location = '" + currKVP.Value + "'");
                    }
                }
            }

            if (filterQueryWhereParams.Count > 0)
            {
                filterQueryString = string.Join(" AND ", filterQueryWhereParams);
                filterQueryString = "WHERE " + filterQueryString;
            }

            string sqlQuery = "SELECT mentor_id, CONCAT_WS(' ',first_name, last_name) AS fullname, degree_name, pathway_name, photo FROM mentor " +
                    "LEFT JOIN lkup_pathway ON mentor.pathway_id = lkup_pathway.pathway_id " +
                    "LEFT JOIN lkup_degree ON mentor.degree = lkup_degree.degree_id " + this.filterQueryString;
            dsFilterMentors = sGlobals.dbWrapper.ExecuteSQLStatement(sqlQuery, ref returnCount);

            //If there were FilterMentors returned, then we display them. Else we display a message that the user has no
            //Filter Mentors selected.
            if (returnCount > 0)
            {
                lblTitle.Content = "ADVANCED FILTER MENTORS";
                lblcount.Content = returnCount.ToString() + " RESULT(S)";
                for (int i = 0; i < dsFilterMentors.Tables[0].Rows.Count; i++)
                {
                    
                    if  (((byte[])dsFilterMentors.Tables[0].Rows[i]["photo"]).Count() == 0)
                    {
                        filterMentors.Add(new Mentor(
                            dsFilterMentors.Tables[0].Rows[i]["fullname"].ToString(),
                            dsFilterMentors.Tables[0].Rows[i]["pathway_name"].ToString(),
                            dsFilterMentors.Tables[0].Rows[i]["degree_name"].ToString(),                           
                            dsFilterMentors.Tables[0].Rows[i]["mentor_id"].ToString()));
                    }
                    else
                    {
                        filterMentors.Add(new Mentor(
                            dsFilterMentors.Tables[0].Rows[i]["fullname"].ToString(),                            
                            dsFilterMentors.Tables[0].Rows[i]["pathway_name"].ToString(),
                            dsFilterMentors.Tables[0].Rows[i]["degree_name"].ToString(),
                            (byte[])dsFilterMentors.Tables[0].Rows[i]["photo"],
                            dsFilterMentors.Tables[0].Rows[i]["mentor_id"].ToString()));
                    }
                }
                lbFilterMentors.ItemsSource = this.filterMentors;
                lbFilterMentors.ScrollIntoView(filterMentors[0]);//go to top of results
            }
            else
            {
                WarningMessage warning = new WarningMessage();
                warning.lblMessage.Content = "There are no mentors for the specified filters.";
                warning.ShowDialog();
                if(tempCB != null)
                    tempCB.SelectedIndex = 0;
            }


            this.pageReady = true;
        }

        private void FilterChanged(object sender, SelectionChangedEventArgs e)
        {
            if (!this.boolStopFilterEvents)
            {
                if (this.pageReady) //xaml builds this event first, then the item binding will fire events before the list is populated...this makes the query not build until the mentor list is originally populated with all the mentors
                {
                    this.buildMentorQuery((ComboBox)sender);
                }
            }
        }

        private void btnBack_Click(object sender, RoutedEventArgs e)
        {
         //   sGlobals.selectedPathwayID.Clear();  nope didn't work.
            sGlobals.focusedAreasOfStudy.Show();
            sGlobals.filterMentors.Hide();
        }

        private void _listBoxItem_MouseLeftButtonDown(object sender, MouseButtonEventArgs e)
        {
            AdvisorBox selectedBox = (AdvisorBox)sender;
            sGlobals.selectedMentorID = selectedBox.mentorID.Content.ToString();
            string viewMentorLogQuery = "INSERT INTO lgd_mentor_student_actions (mentor_id, student_id, date_time, " +
                    "action_performed_id) VALUES ('" + sGlobals.selectedMentorID + "', (SELECT user_id FROM users WHERE username = '" +
                    sGlobals.currentUsername + "'), NOW(), 'V')";

            sGlobals.dbWrapper.ExecuteNonQuery(viewMentorLogQuery);
            sGlobals.mentorDisplay.previousPage = this;
            sGlobals.mentorDisplay.Show();
            sGlobals.filterMentors.Hide();
        }

        private void btnClearFilters_Click(object sender, RoutedEventArgs e)
        {
            //changing the index on all the box would make 6 different query calls and image decryption
            //this makes it only happen once
            this.boolStopFilterEvents = true;
            Filter_Courses_1.SelectedIndex = 0;
            Filter_Education.SelectedIndex = 0;
            Filter_Experience.SelectedIndex = 0;
            Filter_Gender.SelectedIndex = 0;
            Filter_Job_Type.SelectedIndex = 0;
            this.boolStopFilterEvents = false;
            Filter_Location.SelectedIndex = 0;
        }
    }
}
